﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Collections.Specialized;
/// <summary>
/// Summary description for UserDAO
/// </summary>
public class UserDAO:DBAccess, IUser
{

    #region sql command

    string SQL_SELECT_ALL = "select * from UserInfomations";
    string SQL_SELECT_BY_TYPE = "select * from UserInfomations where Type=@type";
    string SQL_SELECT_TYPE_BY_USER_ID = "select Type from UserInfomations where UserID=@id";
    string SQL_SELECT_STAFF = "select * from UserInfomations where Type='Staff' or Type='Manager' or Type='Admin'";
    string SQL_SELECT_LAST_USERID = "SELECT TOP 1 UserID FROM UserInfomations where UserID like(@typeAccount) ORDER BY UserID DESC ";
    string SQL_SELECT_BY_TYPE_1 = "SELECT r.RoleName, u.UserID, u.FullName, u.Birthday, u.Gender, u.Phone, u.Address, u.Email, u.DateJoin,u.Class,u.Course,u.Subject,u.type FROM Roles AS r JOIN Accounts as a ON r.RoleID = a.RoleID join UserInfomations as u on u.UserID = a.Username where type = @type";
    string SQL_SELECT_BY_USER_ID = "select * from UserInfomations where UserID=@id";
    string SQL_INSERT = "insert into UserInfomations(UserID, Fullname, Birthday, Gender, Phone, Address, Email, DateJoin, Class, Course, Subject, Type ) values (@id, @fullname, @birthday, @gender, @phone, @address, @email, @dateJoin, @st_class, @course, @subject, @type)";
    string SQL_UPDATE = "update UserInfomations set Fullname=@fullname, Birthday=@birthday, Gender=@gender, Phone=@phone, Address=@address, Email=@email, DateJoin=@dateJoin, Class=@st_class, Course=@course, Subject=@subject where UserID=@id";
    string SQL_DELETE = "delete from UserInfomations where UserID=@id";
    string SQL_GETNAME = "Select FullName from UserInfomations where UserID=@id";
    string SQL_SELECT_SEARCH_BY_ID = "select * from UserInfomations where UserID = @UserID and Type=@type";
    string SQL_SELECT_SEARCH_BY_NAME = "select * from UserInfomations where FullName like(@name) and type=@type";
    #endregion

    #region fields
    string message;
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion

    public UserDAO()
	{
		//
		// TODO: Add constructor logic here
		//
	}

#region method
    public IList<UserInfo> GetAll()
    {
        IList<UserInfo> list = new List<UserInfo>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    UserInfo user = new UserInfo();
                    user.UserID = dr.GetString(0);
                    user.FullName = dr.GetString(1);
                    user.BirthDay = dr.GetDateTime(2).ToShortDateString();
                    user.Gender = dr.GetString(3);
                    user.Phone = dr.GetString(4);
                    user.Address = dr.GetString(5);
                    user.Email = dr.GetString(6);
                    if (!dr.IsDBNull(7))
                    {
                        user.DateJoin = dr.GetDateTime(7).ToShortDateString();
                    }
                    if (!dr.IsDBNull(8))
                    {
                        user.St_class = dr.GetString(8);
                    }
                    if (!dr.IsDBNull(9))
                    {
                        user.Course = dr.GetString(9);
                    }
                    if (!dr.IsDBNull(10))
                    {
                        user.Subject = dr.GetString(10);
                    }
                    user.Type = dr.GetString(11);
                    list.Add(user);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    public IList<UserInfo> GetAllStaff()
    {
        IList<UserInfo> list = new List<UserInfo>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_STAFF, conn);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    UserInfo user = new UserInfo();
                    user.UserID = dr.GetString(0);
                    user.FullName = dr.GetString(1);
                    user.BirthDay = dr.GetDateTime(2).ToShortDateString();
                    user.Gender = dr.GetString(3);
                    user.Phone = dr.GetString(4);
                    user.Address = dr.GetString(5);
                    user.Email = dr.GetString(6);
                    if (!dr.IsDBNull(7))
                    {
                        user.DateJoin = dr.GetDateTime(7).ToShortDateString();
                    }
                    if (!dr.IsDBNull(8))
                    {
                        user.St_class = dr.GetString(8);
                    }
                    if (!dr.IsDBNull(9))
                    {
                        user.Course = dr.GetString(9);
                    }
                    if (!dr.IsDBNull(10))
                    {
                        user.Subject = dr.GetString(10);
                    }
                    user.Type = dr.GetString(11);
                    list.Add(user);

                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    public DataSet GetUserByType1(string type)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(SQL_SELECT_BY_TYPE_1, conn);
        adapter.SelectCommand.Parameters.AddWithValue("@type", type);
        adapter.Fill(ds);
        return ds;
    }
    public IList<UserInfo> GetUserByType(string type)
    {
        IList<UserInfo> list = new List<UserInfo>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_BY_TYPE, conn);
            command.Parameters.AddWithValue("@type", type);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    UserInfo user = new UserInfo();
                    user.UserID = dr.GetString(0);
                    user.FullName = dr.GetString(1);                    
                    user.BirthDay = dr.GetDateTime(2).ToShortDateString();
                    user.Gender = dr.GetString(3);
                    user.Phone = dr.GetString(4);
                    user.Address = dr.GetString(5);
                    user.Email = dr.GetString(6);
                    if (!dr.IsDBNull(7))
                    {
                        user.DateJoin = dr.GetDateTime(7).ToShortDateString();
                    }
                    if (!dr.IsDBNull(8))
                    {
                        user.St_class = dr.GetString(8);
                    }
                    if (!dr.IsDBNull(9))
                    {
                        user.Course = dr.GetString(9);
                    }
                    if (!dr.IsDBNull(10))
                    {
                        user.Subject = dr.GetString(10);
                    }
                    user.Type = dr.GetString(11);
                    list.Add(user);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    public IList<UserInfo> SearchUser_ByID(string userID, string type)
    {
        IList<UserInfo> list = new List<UserInfo>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_SEARCH_BY_ID, conn);
            command.Parameters.AddWithValue("@type", type);
            command.Parameters.AddWithValue("@UserID", userID);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    UserInfo user = new UserInfo();
                    user.UserID = dr.GetString(0);
                    user.FullName = dr.GetString(1);
                    user.BirthDay = dr.GetDateTime(2).ToShortDateString();
                    user.Gender = dr.GetString(3);
                    user.Phone = dr.GetString(4);
                    user.Address = dr.GetString(5);
                    user.Email = dr.GetString(6);
                    if (!dr.IsDBNull(7))
                    {
                        user.DateJoin = dr.GetDateTime(7).ToShortDateString();
                    }
                    if (!dr.IsDBNull(8))
                    {
                        user.St_class = dr.GetString(8);
                    }
                    if (!dr.IsDBNull(9))
                    {
                        user.Course = dr.GetString(9);
                    }
                    if (!dr.IsDBNull(10))
                    {
                        user.Subject = dr.GetString(10);
                    }
                    user.Type = dr.GetString(11);
                    list.Add(user);
                } 
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

        public IList<UserInfo> SearchUser_ByName(string name, string type)
    {
        IList<UserInfo> list = new List<UserInfo>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_SEARCH_BY_NAME, conn);
            command.Parameters.AddWithValue("@type", type);
            command.Parameters.AddWithValue("@name", name);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    UserInfo user = new UserInfo();
                    user.UserID = dr.GetString(0);
                    user.FullName = dr.GetString(1);
                    user.BirthDay = dr.GetDateTime(2).ToShortDateString();
                    user.Gender = dr.GetString(3);
                    user.Phone = dr.GetString(4);
                    user.Address = dr.GetString(5);
                    user.Email = dr.GetString(6);
                    if (!dr.IsDBNull(7))
                    {
                        user.DateJoin = dr.GetDateTime(7).ToShortDateString();
                    }
                    if (!dr.IsDBNull(8))
                    {
                        user.St_class = dr.GetString(8);
                    }
                    if (!dr.IsDBNull(9))
                    {
                        user.Course = dr.GetString(9);
                    }
                    if (!dr.IsDBNull(10))
                    {
                        user.Subject = dr.GetString(10);
                    }
                    user.Type = dr.GetString(11);
                    list.Add(user);
                } 
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
     
     
    
    public UserInfo GetByID(string id)
    {
        UserInfo user = new UserInfo();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_BY_USER_ID, conn);
            command.Parameters.AddWithValue("@id", id);
            if (Connect())
            {
                /*command.ExecuteReader();
                SqlDataReader dr = command.ExecuteReader();
                 */
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {
                    user.UserID = dr.GetString(0);
                    user.FullName = dr.GetString(1);
                    user.BirthDay = dr.GetDateTime(2).ToShortDateString();
                    user.Gender = dr.GetString(3);
                    user.Phone = dr.GetString(4);
                    user.Address = dr.GetString(5);
                    user.Email = dr.GetString(6);
                    if (!dr.IsDBNull(7))
                    {
                        user.DateJoin = dr.GetDateTime(7).ToShortDateString();
                    }
                    if (!dr.IsDBNull(8))
                    {
                        user.St_class = dr.GetString(8);
                    }
                    if (!dr.IsDBNull(9))
                    {
                        user.Course = dr.GetString(9);
                    }
                    if (!dr.IsDBNull(10))
                    {
                        user.Subject = dr.GetString(10);
                    }
                    user.Type = dr.GetString(11);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return user;
    }

    public int Insert(UserInfo user)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_INSERT, conn);
            command.Parameters.AddWithValue("@id", user.UserID);
            command.Parameters.AddWithValue("@fullname", user.FullName);
            command.Parameters.AddWithValue("@birthday", user.BirthDay);
            command.Parameters.AddWithValue("@gender", user.Gender);
            command.Parameters.AddWithValue("@phone", user.Phone);
            command.Parameters.AddWithValue("@address", user.Address);
            command.Parameters.AddWithValue("@email", user.Email);

            if (user.DateJoin == "")
            {
                command.Parameters.AddWithValue("@dateJoin", DBNull.Value);
            }
            else
                command.Parameters.AddWithValue("@dateJoin", DateTime.Parse(user.DateJoin));

            if (user.St_class == "")
            {
                command.Parameters.AddWithValue("@st_class", DBNull.Value);
            }
            else
                command.Parameters.AddWithValue("@st_class", user.St_class);

            if (user.Course == "")
            {
                command.Parameters.AddWithValue("@course", DBNull.Value);
            }
            else
                command.Parameters.AddWithValue("@course", user.Course);

            if (user.Subject == "")
            {
                command.Parameters.AddWithValue("@subject", DBNull.Value);
            }
            else
                command.Parameters.AddWithValue("@subject", user.Subject);

            command.Parameters.AddWithValue("@type", user.Type);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
               // command.CommandText = SQL_SELECT_BY_USER_ID;
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    public int Update(UserInfo user)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
            command.Parameters.AddWithValue("@id", user.UserID);
            command.Parameters.AddWithValue("@fullname", user.FullName);
            command.Parameters.AddWithValue("@birthday", DateTime.Parse(user.BirthDay));
            command.Parameters.AddWithValue("@gender", user.Gender);
            command.Parameters.AddWithValue("@phone", user.Phone);
            command.Parameters.AddWithValue("@address", user.Address);
            command.Parameters.AddWithValue("@email", user.Email);
            if (user.DateJoin == "")
            {
                command.Parameters.AddWithValue("@dateJoin", DBNull.Value);
            }
            else
                command.Parameters.AddWithValue("@dateJoin", DateTime.Parse(user.DateJoin));

            if (user.St_class == "")
            {
                command.Parameters.AddWithValue("@st_class", DBNull.Value);
            }
            else
                command.Parameters.AddWithValue("@st_class", user.St_class);

            if (user.Course == "")
            {
                command.Parameters.AddWithValue("@course", DBNull.Value);
            }
            else
                command.Parameters.AddWithValue("@course", user.Course);

            if (user.Subject == "")
            {
                command.Parameters.AddWithValue("@subject", DBNull.Value);
            }
            else
                command.Parameters.AddWithValue("@subject", user.Subject);

         //   command.Parameters.AddWithValue("@type", user.Type);

            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    public int Delete(string userID)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_DELETE, conn);
            command.Parameters.AddWithValue("@id",userID);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }
    public string GetLast_UserID(string typeaccount)
    {
        string userID="";
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_LAST_USERID, conn);
            command.Parameters.AddWithValue("@typeAccount", typeaccount);
            if (Connect())
            {
                userID = (string)command.ExecuteScalar();                
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        
        return userID;
    }

    public void DeleteUsers(StringCollection sc)
    {
    //    SqlConnection conn = new SqlConnection(GetConnectionString());
        StringBuilder sb = new StringBuilder(string.Empty);

        foreach (string item in sc)
        {
            const string sqlStatement = "DELETE FROM UserInfomations WHERE UserID";
            sb.AppendFormat("{0}='{1}'; ", sqlStatement, item);
        }
        try
        {
          //  conn.Open();
            if (Connect())
            {
                SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
            }
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Deletion Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
           // conn.Close();
            Disconnect();
        }
    }

    public string GetTypeByID(string id)
    {
        string Type = "";
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_TYPE_BY_USER_ID, conn);
            command.Parameters.AddWithValue("@id", id);
            if (Connect())
            {
                /*command.ExecuteReader();
                SqlDataReader dr = command.ExecuteReader();
                 */
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {   
                   Type = dr.GetString(0);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return Type;
    }

    public string GetName(string id)
    {
        string name = "";
        try
        {
            SqlCommand command = new SqlCommand(SQL_GETNAME, conn);
            command.Parameters.AddWithValue("@id", id);
            if (Connect())
            {
                
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {
                    name = dr.GetString(0);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return name;
    }


#endregion
}